from sqlalchemy import text
import json
from flask import Blueprint, render_template, request,current_app,jsonify
from applications.extensions import db
def query_one(sql,localfileds={}):
    '''
    res = {
        'msg': msg,
        'code': 0,
        'data': data,
        'count': count,
        'total_count':0, //全部的数量
        'limit': limit,
        'fileds': fileds
                fileds=[ {'value' : column['name'],'text':column['name'],
                            'column_type' : str(column['type'])} for column in query_all.column_descriptions]
    }
    '''
    res={'msg': '',
        'code': 0,
        'data': [],
        'count': 0,
        'limit': 10,
         'page':1,
         'total_count':0,
        'fileds': []}
    limit=int(request.args.get('limit',10))
    res['limit']=limit
    page=int(request.args.get('page',1))
    res['page'] = page
    contions=json.loads(request.args.get('contions','{}'))

    where=[] #条件语句
    if contions:
        if contions.get('contions_type')=='input': #文本输入框
            if contions.get('contions_value')=='like':
                where.append(f"{contions.get('fileds_title')} like '%{contions.get('seach_content')}%' ")
            elif  contions.get('contions_value')=='=':
                where.append(f"{contions.get('fileds_title')} = '{contions.get('seach_content')}' ")
        elif contions.get('contions_type') in ['int','number','decimal'] : #数字
                where.append(f"{contions.get('fileds_title')} {contions.get('contions_value')} {contions.get('seach_content')} ")
        elif contions.get('contions_type') in ['date','datetime'] or contions.get('contions_type')=='DATE': #时间
            if contions.get('contions_value') == 'between':
                where.append(f"""({contions.get('fileds_title')} {contions.get('contions_value')} 
                '{contions.get('seach_content')[0]}' and '{contions.get('seach_content')[1]}') """)
            else:
                where.append(f"{contions.get('fileds_title')}  {contions.get('contions_value')}  '{contions.get('seach_content')}' ")

    if where:
        where='where '+'and'.join(where)
    else:
        where=' '
    # 获取记录数量
    count_sql = text(f'SELECT COUNT(*) FROM ({sql} {where}) t2 ')
    count_result = db.session.execute(count_sql)
    total_count = count_result.scalar()
    res['total_count'] = total_count
    # 构建分页的 SQL 语句
    offset = (page - 1) * limit
    sql=f"{sql} {where} LIMIT {limit} OFFSET {offset}"

    try:
        results = db.session.execute(text(sql))
        res['msg']='成功'

        # # 获取字段名称
        # field_names = results.keys()

        #获取字段类型
        # 创建临时表
        create_temp_table_sql = f"CREATE TEMPORARY TABLE temp_view AS {sql}"
        db.session.execute(text(create_temp_table_sql))

        # 查询临时表的列信息
        column_info_sql = " SHOW  columns from temp_view "
        temp_results = db.session.execute(text(column_info_sql))

        field_info = []
        for temp_row in temp_results:
            field_info.append({
                'value': temp_row[0],
                'column_type': temp_row[1],
                'text':localfileds.get(temp_row[0]) if localfileds.get(temp_row[0]) else temp_row[0]
            })

        res['fileds'] = field_info
        # 获取查询结果内容
        content = []
        for row in results:
            dict_row={}
            for k,v in row._mapping.items():
                if v:
                    dict_row[k]=str(v)
                else:
                    dict_row[k] = ''

            content.append(dict_row)
        res['data']  = content
        res['count'] = results.rowcount
    except Exception as e:
        print(e)
        res['msg'] = f'失败:{e}'
        res['code'] = 400
    finally:
        # 删除临时表
        drop_temp_table_sql = "DROP TEMPORARY TABLE IF EXISTS temp_view"
        db.session.execute(text(drop_temp_table_sql))
        # 提交事务
        db.session.commit()

    return jsonify(res)


def query_one_sample(sql):
    '''

    res = {
        'msg': msg,
        'code': 0,
        'data': data,
        'total_count':0, //全部的数量
        'fileds': fileds
                fileds=[ {'value' : column['name'],'text':column['name'],
                            'column_type' : str(column['type'])} for column in query_all.column_descriptions]
    }
    '''
    res={'msg': '',
        'code': 0,
        'data': [],
         'total_count':0,
        'fileds': []}

    # 获取记录数量
    count_sql = text(f'SELECT COUNT(*) FROM ({sql} ) t2 ')
    count_result = db.session.execute(count_sql)
    total_count = count_result.scalar()
    res['total_count'] = total_count
    # 构建分页的 SQL 语句



    try:
        results = db.session.execute(text(sql))
        res['msg']='成功'

        # # 获取字段名称
        # field_names = results.keys()

        #获取字段类型
        # 创建临时表
        create_temp_table_sql = f"CREATE TEMPORARY TABLE temp_view AS {sql}"
        db.session.execute(text(create_temp_table_sql))

        # 查询临时表的列信息
        column_info_sql = " SHOW  columns from temp_view "
        temp_results = db.session.execute(text(column_info_sql))

        field_info = []
        for temp_row in temp_results:
            field_info.append({
                'value': temp_row[0],
                'column_type': temp_row[1],

            })

        res['fileds'] = field_info
        # 获取查询结果内容
        content = []
        for row in results:
            dict_row={}
            for k,v in row._mapping.items():
                if v:
                    dict_row[k]=str(v)
                else:
                    dict_row[k] = ''

            content.append(dict_row)
        res['data']  = content
        res['count'] = results.rowcount
    except Exception as e:
        print(e)
        res['msg'] = f'失败:{e}'
        res['code'] = 400
    finally:
        # 删除临时表
        drop_temp_table_sql = "DROP TEMPORARY TABLE IF EXISTS temp_view"
        db.session.execute(text(drop_temp_table_sql))
        # 提交事务
        db.session.commit()

    return jsonify(res)



